package dao.impl;

import bean.dto.ExpenseTypePage;
import bean.pojo.ExpenseType;
import bean.pojo.User;
import bean.vo.ExpenseTypeOption;
import commons.jdbc.basic.BasicDao;
import commons.jdbc.basic.JDBCUtil;
import commons.xlhUtils.MyFormatUtils;
import dao.ExpenseTypeDao;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * dao.impl
 * User: xlh
 * Date: 2024/12/17 9:56
 * motto:推到高塔
 * Description:
 * Version: V1.0
 */
public class ExpenseTypeDaoImpl implements ExpenseTypeDao {

    @Override
    public List<ExpenseType> findByPage(ExpenseTypePage expenseTypePage) {
        //sql
        StringBuffer   sql=new StringBuffer("select * from  expense_type  where  1=1 ");
        //集合参数
        List<Object>  parameters=new ArrayList<>();
        //验证是否为空  null  "    "
        if(!MyFormatUtils.isBank(expenseTypePage.getTypeTime())){
            parameters.add(expenseTypePage.getTypeTime());
            sql.append(" and  type_time like  concat('%',?,'%')");
        }
        sql.append(" order by  type_time  desc");
        sql.append(" limit "+(expenseTypePage.getPage()-1)*expenseTypePage.getLimit()+","+expenseTypePage.getLimit());
        System.out.println("sql:"+sql);
        System.out.println("参数:"+parameters);
        JDBCUtil jdbcUtil=new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());
        if(query.size()>0){
            List<ExpenseType> list=new ArrayList<>();
            for (Map<String, Object> row : query) {//Community
                ExpenseType expenseType=new ExpenseType();
                expenseType.setTypeId((Integer) row.get("type_id"));
                expenseType.setTypeName((String) row.get("type_name"));
                expenseType.setTypePrice((Double) row.get("type_price"));
                expenseType.setTypeTime((String) row.get("type_time"));
                expenseType.setTypeDescription((String) row.get("type_description"));
                list.add(expenseType);
            }
            return  list;
        }
        return null;
    }

    @Override
    public Long findByTotal(ExpenseTypePage expenseTypePage) {
        StringBuffer   sql=new StringBuffer("select  count(*) ct  from  expense_type  where  1=1 ");
        //集合参数
        List<Object>  parameters=new ArrayList<>();
        //验证是否为空  null  "    "
        if(expenseTypePage.getTypeTime()!=null){
            parameters.add(expenseTypePage.getTypeTime());
            sql.append(" and  type_time like  concat('%',?,'%')");
        }
        System.out.println("sql:"+sql);
        System.out.println("参数:"+parameters);
        JDBCUtil jdbcUtil=new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql.toString(), parameters.toArray());
        //默认0条
        Long  ct=0L;
        if(query.size()>0){
            //改变
            ct=(Long)query.get(0).get("ct");
        }
        return ct;
    }

    @Override
    public List<ExpenseTypeOption> findAllList() {
        String  sql="select type_id,type_name from  expense_type GROUP BY type_name";
        JDBCUtil  jdbcUtil=new JDBCUtil();
        List<Map<String, Object>> query = jdbcUtil.query(sql);
        if(query.size()>0){
            List<ExpenseTypeOption> list=new ArrayList<>();
            for (Map<String, Object> row : query) {//Community
                ExpenseTypeOption  expenseTypeOption=new ExpenseTypeOption();
                expenseTypeOption.setTypeId((Integer) row.get("type_id"));
                expenseTypeOption.setTypeName((String) row.get("type_name"));
                list.add(expenseTypeOption);
            }
            return  list;
        }
        return null;
    }
    public int insert(ExpenseType expenseType) {
        String insert1 = "insert into expense_type (type_name, type_price, type_time, type_description)values (?,?,?,?)";
        String select = "select user_id,room_id from user";
        String select2 = "SELECT type_id FROM expense_type WHERE type_name =? ORDER BY type_id DESC LIMIT 1"; // 去除原不合理的GROUP BY，采用正确的查询方式获取type_id
        String insert2 = "insert into charge_record (user_id,room_id, type_id, charge_amount, charge_year_month)values (?,?,?,?,?)";
        JDBCUtil jdbcUtil = new JDBCUtil();
        try {
            // 插入expense_type记录
            int update = jdbcUtil.update(insert1, expenseType.getTypeName(), expenseType.getTypePrice(), expenseType.getTypeTime(), expenseType.getTypeDescription());
            if (update > 0) {  // 确保插入成功后再进行后续操作
                // 查询刚插入的expense_type记录的type_id，使用预编译参数绑定避免SQL注入
                List<Map<String, Object>> querytype = jdbcUtil.query(select2, expenseType.getTypeName());
                Integer typeId = null;
                if (!querytype.isEmpty()) {
                    typeId = (Integer) querytype.get(0).get("type_id");
                }
                if (typeId!= null) {  // 只有获取到type_id才进行后续插入charge_record的操作，避免空指针
                    // 获取所有user_id和room_id，一次遍历获取两个字段的值
                    List<Map<String, Object>> query = jdbcUtil.query(select);
                    List<Integer> userIds = new ArrayList<>();
                    List<Integer> roomIds = new ArrayList<>();
                    for (Map<String, Object> row : query) {
                        userIds.add((Integer) row.get("user_id"));
                        roomIds.add((Integer) row.get("room_id"));
                    }
                    // 插入charge_record记录
                    for (int i = 0; i < userIds.size(); i++) {
                        jdbcUtil.update(insert2, userIds.get(i), roomIds.get(i), typeId, expenseType.getTypePrice(), expenseType.getTypeTime());
                    }
                }
            }
            return update;
        } catch (Exception e) {
            // 处理数据库操作异常，这里可以添加更详细的日志记录等操作
            e.printStackTrace();
            return -1;  // 根据实际需求返回合适的表示失败的值
        } finally {
            // 确保关闭数据库资源，避免资源泄露
            jdbcUtil.close();  // 假设JDBCUtil中有合适的close方法来关闭数据库相关连接等资源
        }
    }
    @Override
    public int updateById(ExpenseType expenseType) {
        String sql = "UPDATE expense_type " +
                "SET type_name =?,type_description =?," +
                "type_price =?, type_time =? " +
                "WHERE type_id =?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        int update = jdbcUtil.update(sql, expenseType.getTypeName(), expenseType.getTypeDescription(), expenseType.getTypePrice(), expenseType.getTypeTime(), expenseType.getTypeId());
        return update;
    }


    @Override
    public int deleteById(Integer typeId) {
        String sql = "DELETE FROM expense_type WHERE type_id =?";
        String sql2 = "DELETE FROM charge_record WHERE type_id =?";
        JDBCUtil jdbcUtil = new JDBCUtil();
        int delete=jdbcUtil.update(sql,typeId);
        int delete2=jdbcUtil.update(sql2,typeId);
        if(delete+delete2>=2){
            return delete;
        }
        return 0;
    }

    @Override
    public int createType(String typeName) {
        String select = "select type_name  from expense_type";
        String sql = "insert  expense_type (type_name,type_description)" +
                " values (?,?)";
        JDBCUtil jdbcUtil=new JDBCUtil();
        ExpenseType expenseType=new ExpenseType();
        List<Object>  parameters=new ArrayList<>();
        List<Map<String, Object>> query = jdbcUtil.query(select.toString(), parameters.toArray()); if(query.size()>0){
            List<ExpenseType> list=new ArrayList<>();
            for (Map<String, Object> row : query) {//Community
                expenseType.setTypeName((String) row.get("type_name"));
                list.add(expenseType);
            }
        }
        if(typeName.equals(expenseType.getTypeName())){
            return 0;
        }else {
            String typeDescription="这是根类，请谨慎删除!";
            int update = jdbcUtil.update(sql,typeName,typeDescription);
            return update;
        }
    }
}
